﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Hp.Vertica.Migration.SqlServer
{
    public abstract class TableCollection<TTableType> where TTableType : TableBase
    {
        public TableCollection(Database db)
        {
            this.Database = db;
        }
        protected abstract String QueryText { get; }
        public Database Database { get; private set; }

        private Dictionary<String, TTableType> _tablesCache;

        public List<TTableType> Tables
        {
            get
            {
                if (_tablesCache == null)
                {
                    this.Refresh();
                }
                return _tablesCache.Values.OrderBy(c => c.Schema.Name).ThenBy(c => c.Name).ToList();
            }
        }

        public TTableType this[String key]
        {
            get
            {
                if (_tablesCache == null)
                {
                    this.Refresh();
                }
                return this._tablesCache[key.ToUpper()];
            }
        }

        public void Refresh()
        {
            Dictionary<String, TTableType> current = new Dictionary<string, TTableType>();
            Dictionary<String, Schema> schemas = new Dictionary<string, Schema>();
            using (SqlConnection conn = new SqlConnection(this.Database.DataSource.ConnectionString))
            {
                conn.Open();
                conn.ChangeDatabase(this.Database.Name);
                var cmd = conn.CreateCommand();
                cmd.CommandText = QueryText;
                var dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    String sName = dr.GetString(0);
                    String name = dr.GetString(1);
                    String key = (sName + "." + name).ToUpper();
                    if (_tablesCache != null && _tablesCache.ContainsKey(key))
                    {
                        current.Add(key, _tablesCache[key]);
                    }
                    else
                    {
                        Schema s = this.Database.SchemaCollection[sName.ToUpper()];
                        if (!this._schemas.ContainsKey(s.Name.ToUpper()))
                        {
                            _schemas.Add(s.Name.ToUpper(), s);
                        }
                        UserTable ut = new UserTable()
                        {
                            Name = name,
                            CreatedOn = dr.GetDateTime(2),
                            ModifiedOn = dr.GetDateTime(3),
                            Database = this.Database,
                            Schema = s
                        };
                    }
                }
            }
            _tablesCache = current;
            _schemas = schemas;
        }

        private Dictionary<String, Schema> _schemas;

        public List<Schema> Schemas
        {
            get
            {
                if (this._schemas == null)
                {
                    this.Refresh();
                }
                return _schemas.Values.OrderBy(c => c.Name).ToList();
            }
        }
    }
}
